package DBLayer;
import ModelLayer.Attribute;
import java.sql.*;
import java.util.ArrayList;

public class DBAttribute {
    // <editor-fold defaultstate="collapsed" desc="Attributes & Constructors">
    private Connection conn;

    public DBAttribute() {
        conn = SQLConnector.getInstance().getConnection();
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL SELECT Methods">
    public Attribute getAttributeByID(int id) {
        return selectSingle("SELECT `id_attribute`, `id_attribute_group`, `color`, `name` FROM `ps_attribute` LEFT JOIN `ps_attribute_lang` USING (`id_attribute`) WHERE (`id_lang` = " + Constants.DEFAULT_LANGUAGE + " OR `id_lang` IS NULL) AND `id_attribute` = " + id + " LIMIT 0, 1;");
    }

    public ArrayList<Attribute> getAttributeList() {
        return selectMultiple("SELECT `id_attribute`, `id_attribute_group`, `color`, `name` FROM `ps_attribute` LEFT JOIN `ps_attribute_lang` USING (`id_attribute`) WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " OR `id_lang` IS NULL ORDER BY `id_attribute` ASC;");
    }

    public ArrayList<Integer> getAttributeIDsByProductAttributeID(int id) {
        return selectIDs("SELECT `id_attribute` FROM `ps_product_attribute_combination` WHERE `id_product_attribute` = " + id + " ORDER BY `id_attribute` ASC;");
    }

    private Attribute selectSingle(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        Attribute attribute = new Attribute();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            if (result.next()) {
                attribute = buildAttribute(result);
            }
            System.out.println("Fetching Attribute Object");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return attribute;
    }

    private ArrayList<Attribute> selectMultiple(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Attribute> attributes = new ArrayList<Attribute>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                Attribute attribute = new Attribute();
                attribute = buildAttribute(result);
                attributes.add(attribute);
            }
            System.out.println("Fetching Attribute List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return attributes;
    }

   private ArrayList<Integer> selectIDs(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Integer> ids = new ArrayList<Integer>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                ids.add(result.getInt("id_attribute"));
            }
            System.out.println("Fetching Attribute ID List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return ids;
    }

    private Attribute buildAttribute(ResultSet result) {
        DBProductAttribute dbProductAttribute = new DBProductAttribute();
        Attribute attribute = new Attribute();
        try {
            attribute.setIDAttribute(result.getInt("id_attribute"));
            attribute.setIDAttributeGroup(result.getInt("id_attribute_group"));
            attribute.setColor(result.getString("color"));
            attribute.setName(result.getString("name"));
            attribute.setProductAttributeIDs(dbProductAttribute.getProductAttributeIDsByAttributeID(result.getInt("id_attribute")));

        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
        return attribute;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL INSERT, UPDATE & DELETE Methods">
    public int addAttribute(Attribute attribute) {
        int returnID = 0;
        Statement stmt = null;
        ResultSet result = null;

        String query = "INSERT INTO `ps_attribute_lang` (`id_attribute`, `id_lang`, `name`) "
                + "VALUES (" + attribute.getIDAttribute() + ", " + Constants.DEFAULT_LANGUAGE + ", '" + attribute.getName() + "');";

        query += "INSERT INTO `ps_attribute` (`id_attribute`, `id_attribute_group`, `color`) "
                + "VALUES (" + attribute.getIDAttribute() + ", "
                + "VALUES (" + attribute.getIDAttributeGroup() + ", "
                + "VALUES (" + attribute.getColor() + ");";
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            result = stmt.getGeneratedKeys();
            if (result.next()) {
                returnID = result.getInt(1);
            }
            System.out.println("Added Attribute ID " + returnID);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public int updateAttribute(Attribute attribute, int id) {
        int returnID = 0;
        Statement stmt = null;
	String query = "UPDATE `ps_attribute` SET "
                + "`id_attribute`       = "  + attribute.getIDAttribute() + ", "
                + "`id_attribute_group` = "  + attribute.getIDAttributeGroup() + ", "
                + "`color`              = '" + attribute.getColor() + "' "
                + "WHERE `id_attribute` = " + id + " LIMIT 1; ";

        query += "UPDATE `ps_attribute_lang` SET "
                + "`id_attribute`   = "  + attribute.getIDAttribute() + ", "
                + "`name`         = "  + attribute.getName() + " "
                + "WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " AND `id_attribute` = " + id + " LIMIT 1;";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            returnID = attribute.getIDAttribute();
            System.out.println("Updated Attribute ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public void deleteAttribute(int id) {
        Statement stmt = null;

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM `ps_attribute` WHERE `id_attribute` = " + id + " LIMIT 1; DELETE FROM `ps_attribute_lang` WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " AND `id_attribute` = " + id + " LIMIT 1;");
            System.out.println("Deleted Attribute ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
    }
    // </editor-fold>
}
